package HelperFinancial.DataBase;

import HelperFinancial.Class.Credito;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class CreditoHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME="creditos.db";
	private static final int SCHEMA_VERSION=1;
	
	public CreditoHelper(Context context) {
		super(context, DATABASE_NAME, null, SCHEMA_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE credits (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, value REAL, qntdMeses INTEGER, date TEXT, " +
										 "corrente INTEGER, pai INTEGER);");
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	
	public void insertCredit(Credito credito) {
		ContentValues cv = new ContentValues();
		cv.put("name", credito.getName());
		cv.put("value", credito.getValue());
		cv.put("qntdMeses", credito.getQntdMeses());
		cv.put("date", credito.getDateDuration());
		cv.put("corrente", credito.getQntdCorrente());
		cv.put("pai", credito.getIdPai());
		getWritableDatabase().insert("credits", "name", cv);
	}
	
	public Cursor getAllCredits() {
		return(getReadableDatabase()
		.rawQuery("SELECT * FROM credits",
		null));
	}
	
	public Cursor getCreditByName(String name){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM credits WHERE name = '" + name + "'",
				null));
	}
	
	public Cursor getCreditById(Integer id){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM credits WHERE _id = " + id,
				null));
	}
	
	public Cursor getCreditsSum() {
		return(getReadableDatabase()
				.rawQuery("SELECT SUM(value) AS valueTotal FROM credits",
				null));
	}
	
	public Cursor getCreditsByDate(String date) {
		return(getReadableDatabase()
				.rawQuery("SELECT SUM(value) AS valueTotal FROM credits WHERE SUBSTR(date, 4) = '" + date + "'",
				null));
	}
	
	public Cursor getCreditsIds() {
		return(getReadableDatabase()
				.rawQuery("SELECT MAX(_id) AS maiorId FROM credits",	null));
	}
	
	public int getLastId() {
		Cursor cr = getCreditsIds();
		
		int colValue = cr.getColumnIndex("maiorId");
		
		cr.moveToFirst();
		int value = cr.getInt(colValue);
		
		return value;
	}
	
	public void removeAllCredits() {
		getReadableDatabase().delete("credits", null, null);
	}
	
	public void removeCredit(Integer Id){
		getReadableDatabase().delete("credits", "_id=" + Id, null);
	}
	
	public void updateCredit(Credito credito){
		ContentValues cv = new ContentValues();
		cv.put("name", credito.getName());
		cv.put("value", credito.getValue());
		cv.put("qntdMeses", credito.getQntdMeses());
		cv.put("date", credito.getDateDuration());
		cv.put("corrente", credito.getQntdCorrente());
		cv.put("pai", credito.getIdPai());
		getReadableDatabase().update("credits", cv, "_id=" + credito.getId(), null);
	}
	
	public void updateCreditIdPai(Credito credito, int id){
		ContentValues cv = new ContentValues();
		cv.put("name", credito.getName());
		cv.put("value", credito.getValue());
		cv.put("qntdMeses", credito.getQntdMeses());
		cv.put("date", credito.getDateDuration());
		cv.put("corrente", credito.getQntdCorrente());
		cv.put("pai", credito.getIdPai());
		getReadableDatabase().update("credits", cv, "_id=" + id, null);
	}
	
	public String getCreditName(Cursor c) {
		return(c.getString(1));
	}
	
	public double getCreditValue(Cursor c) {
		return(c.getDouble(2));
	}
	
	public String getCreditDate(Cursor c) {
		return(c.getString(4));
	}
	
	public int getCreditQntdMeses(Cursor c) {
		return(c.getInt(3));
	}
	
	public int getCreditQntdCorrente(Cursor c) {
		return(c.getInt(4));
	}
	
	public int getCreditIdPai(Cursor c) {
		return(c.getInt(5));
	}
	
	public Cursor getFilhosHabilitados(int qntdCorrente, int idPai) {
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM credits WHERE corrente>=" + qntdCorrente + " and pai=" + idPai, null));
	}
	
	public Cursor getCreditsByFilter(String date) {
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM credits WHERE date LIKE '%" + date +
						  "%'", null));
	}
	
	public Cursor getCreditsByFilter(String date, String type){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM credits WHERE date LIKE '%" + date +
						  "%'", null));// AND type = '" + type + "' GROUP BY type", null));
	}
	
	public Cursor getCreditsSumByDate(String date){
		return(getReadableDatabase()
				.rawQuery("SELECT SUM(value) AS valueTotal FROM credits WHERE date LIKE '%" + date +
						  "%'", null));
	}
	
}
